﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using CruzDelSur.Dominio;
using CruzDelSur.Models;
using NHibernate;
using NHibernate.Criterion;

namespace CruzDelSur.Persistencia
{
    public class ReporteServiciosDAO
    {

        public ICollection<ReportServicioTramoResult> reporteServicios(ReportesServiciosModel model)
        {
            string codEstado = Servicio.ACTIVO;
            string query = "";
            if (model.FlagBaja) codEstado = Servicio.INACTIVO ;
            if (model.FlagTodos) codEstado = "%";
            if (model.TipoReporte == 1)
            {
                query += "select st.codServicio+' '+se.desServicio as campo1,st.codTramo+' '+ts.desUbicacion+'-'+td.desUbicacion as campo2";
            }
            else
            {
                query += "select st.codTramo+' '+ts.desUbicacion+'-'+td.desUbicacion as campo1,st.codServicio+' '+se.desServicio as campo2";
            }
            query+= " from SE_TB_Servicio_Tramo st " +
                    " inner join SE_TB_Servicio se on st.codServicio=se.codServicio " +
                    " inner join SE_TB_Tramo tr on st.codTramo=tr.codTramo " +
                    " inner join SE_TB_Ubicacion ts on tr.codUbicacion=ts.codUbicacion " +
                    " inner join SE_TB_Ubicacion td on tr.SE_TB_Ubicacion_codUbicacion=td.codUbicacion " +
                    " where (st.codServicio like :scodser) and (st.codTramo like :scodtra) and ((se.estServicio like :scodest) or (tr.estTramo like :scodest))";
            if (model.TipoReporte == 1)
            {
                query += " order by st.codServicio,st.codTramo";
            }
            else
            {
                query += " order by st.codTramo,st.codServicio";
            }
            using (ISession sesion = NHibernateHelper.AbrirSesion())
            {
                ICollection<ReportServicioTramoResult> resultado = sesion.CreateSQLQuery(query)
                .SetParameter("scodser", model.CodServicio)
                .SetParameter("scodtra", model.CodTramo)
                .SetParameter("scodest", codEstado)
                .SetResultTransformer(new NHibernate.Transform.AliasToBeanResultTransformer(typeof(ReportServicioTramoResult)))
                .List<ReportServicioTramoResult>();
                 return resultado;
            }
        }

        public ICollection<ReporteBarraResult> reporteServiciosGrafico(ReportesServiciosModel model)
        {
            string query = "";
            if (model.TipoReporte == 1)
            {
                query = "SELECT sv.desServicio as Categoria, count(st.codTramo) as Cantidad" +
                       " FROM SE_TB_Servicio_Tramo as st" +
                       " INNER JOIN SE_TB_Servicio as sv ON st.codServicio = sv.codServicio" +
                       " GROUP BY sv.desServicio";
            }
            else
            {
                query = "SELECT tr.desTramo as Categoria, count(st.codServicio) as Cantidad " +
                       " FROM dbo.SE_TB_Servicio_Tramo as st " +
                       " INNER JOIN SE_TB_Tramo as tr ON st.codTramo = tr.codTramo " +
                       " GROUP BY tr.desTramo";
            }

            using (ISession sesion = NHibernateHelper.AbrirSesion())
            {
                ICollection<ReporteBarraResult> resultado = sesion.CreateSQLQuery(query)
                //.SetParameter("scodser", model.CodServicio)
                //.SetParameter("scodtra", model.CodTramo)
                //.SetParameter("scodest", codEstado)
                .SetResultTransformer(new NHibernate.Transform.AliasToBeanResultTransformer(typeof(ReporteBarraResult)))
                .List<ReporteBarraResult>();
                return resultado;
            }
        }
    }
}